import { add_table, add_index, alter_tables } from "./createdb";

//全局变量数据库名称
const database_name = "mydata";
//全局变量地址
const database_path = '_doc/mydata.db';

//建立映射关系
const globalColumnsMap = {}


//我这个封装通过promise返回出去！！！
//创建数据库或者有该数据库就打开,    这一步必须要！
function openSqlite(){
	//创建数据库或者打开
	//这plus.sqlite只在手机上运行
	return new Promise((resolve,reject) =>{
		plus.sqlite.openDatabase({
			name: database_name,  //数据库名称
			path: database_path,   //数据库地址，uniapp推荐以下划线为开头，这到底存在哪里去了，我也不清楚，哈哈
			success(e){
				resolve(e); //成功回调
			},
			fail(e){
				reject(e); //失败回调
			}
		})
	})	
}

//关闭数据库
function closeSQL(name){
	return new Promise((resolve,reject) =>{
		plus.sqlite.closeDatabase({
			name:name,
			success(e){
				resolve(e);
			},
			fail(e){
				reject(e);
			}
		})
	})
}

//监听数据库是否开启
function isOpen(name,path){
	var ss = name || database_name;
	var qq = path || database_path;
	//数据库打开了就返回true,否则返回false
	var open = plus.sqlite.isOpenDatabase({
					name:ss,
					path:qq
				})
	return open;
}

// 执行增删改SQL语句
function executeSQL(sql){
	return new Promise((resolve,reject)=>{
		console.log('\x1b[32m'+ "executeSQL执行sql语句:"+ '\x1b[0m' + sql + "无参数")
		plus.sqlite.executeSql({
			name: database_name,
			sql: sql,
			success(e){
				console.log("执行SQL成功", e);
				resolve(e);
			},
			fail(e){
				console.error("执行SQL失败", e);
				reject(e);
			}
		});
	});
}
//执行增删改SQL语句携带参数，并返回数据
function executeSQLParam(sql, params){
	return new Promise((resolve,reject) =>{
		console.log('\x1b[32m'+ "executeSQLParam执行sql语句:"+ '\x1b[0m' + sql + "参数: [" + params + "] ")
		//将参数放到sql语句里，用?占位符代替，然后将参数数组传进去
		//预处理
		sql = prepareSql(sql, params);
		console.log("替换后的sql语句:" + sql)
		plus.sqlite.executeSql({
			name: database_name,
			sql: sql,
			success(e){
				resolve(e);
				console.log("成功",e);
			},
			fail(e){
				reject(e);
				console.error("失败",e);
			}
		})
	})
}
// 执行查SQL语句
function selectSQL(sql){
	return new Promise((resolve,reject)=>{
		console.log('\x1b[32m'+ "executeSQL执行sql语句:"+ '\x1b[0m' + sql + "无参数")
		plus.sqlite.selectSql({
			name: database_name,
			sql: sql,
			success(e){
				console.log("执行SQL成功", e)
				resolve(e)
			},
			fail(e){
				console.error("执行SQL失败", e)
				reject(e)
			}
		});
	});
}
//执行查SQL语句携带参数，并返回数据
function selectSQLParam(sql, params){
	return new Promise((resolve,reject) =>{
		console.log('\x1b[32m'+ "executeSQLParam执行sql语句:"+ '\x1b[0m' + sql + "参数: [" + params + "] ")
		//将参数放到sql语句里，用?占位符代替，然后将参数数组传进去
		//预处理
		sql = prepareSql(sql, params);
		console.log("替换后的sql语句:" + sql)
		plus.sqlite.selectSql({
			name: database_name,
			sql: sql,
			success(e){
				resolve(e);
				console.log("成功",e);
			},
			fail(e){
				reject(e);
				console.error("失败",e);
			}
		})
	})
}
//插入
const insert = (sqlPrefix, tableName, data) => {
    const columnsMap = globalColumnsMap[tableName];
    const dbColumns = [];
    const params = [];
    for(let item in data){
        if(data[item] != undefined && columnsMap[item] != undefined){
            dbColumns.push(columnsMap[item]);
            params.push(data[item])
        }
    }
    const preper = "?".repeat(dbColumns.length).split("").join(",");
    const sql = `${sqlPrefix} ${tableName}(${dbColumns.join(",")})values(${preper})`;
    return executeSQLParam(sql, params)
}
//插入或忽略
const insetOrIgnore = (tableName, data) => {
    return insert("insert or ignore into", tableName, data)
} 
//插入或替换
const insertOrReplace = (tableName, data) => {
    return insert("insert or replace into", tableName, data)
}

//查询数据并进行下划线转驼峰命名法
function queryAll(sql, params){
	return new Promise((resolve,reject) =>{
		console.log('\x1b[32m' + "queryAll执行sql语句:" + '\x1b[0m' + sql + " 参数:" + "[ " + params + " ]")
		//将参数放到sql语句里，用?占位符代替，然后将参数数组传进去
		//预处理
		sql = prepareSql(sql, params);
		console.log("替换后的sql语句:" + sql)
		plus.sqlite.selectSql({
			name: database_name,
			sql: sql,
			success(e){
				e.forEach((item, index) => {
					e[index] = convertDbObjBiz(item)
				})
				console.log("成功",e)
				resolve(e)
			},
			fail(e){
				reject(e);
				console.error("失败",e);
			}
		})
	})
}

//查询数量
const queryCount = (sql, params) => {
    return new Promise((resolve, _reject) => {
        console.log('\x1b[32m' + "queryCount执行sql语句: " + '\x1b[0m'+ sql + " 参数:" + " [" + params + "] ")
        //将参数放到sql语句里，用?占位符代替，然后将参数数组传进去
		//预处理
		sql = prepareSql(sql, params);
		console.log("替换后的sql语句:" + sql)
		plus.sqlite.selectSql({
			name: database_name,
			sql: sql,
			success(e){
				console.log("成功",e)
				resolve(e[0].totalCount)
			},
			fail(e){
				_reject(e);
				console.error("失败",e);
			}
		})
    })
}

//更新
const update = (tableName, data, paramCondition) => {
    const columnsMap = globalColumnsMap[tableName];
    const dbColumns = [];
    const params = [];
    const whereColumns = [];
    for(let item in data){
        if(data[item] != undefined && columnsMap[item] != undefined){
            dbColumns.push(`${columnsMap[item]}=?`);
            params.push(data[item])
        }
    }
    for(let item in paramCondition){
        if(paramCondition[item]){
            params.push(paramCondition[item])
            whereColumns.push(`${columnsMap[item]}=?`)
        }
    }
    const sql = `update ${tableName} set ${dbColumns.join(",")} ${whereColumns.length > 0 ? 'where' : ''} ${whereColumns.join(" and ")}`;
    return executeSQLParam(sql, params)
}
//查询一条
const queryOne = (sql, params) => {
    return  new Promise((reslove, _reject) => {
        console.log('\x1b[32m'+ "queryOne执行sql语句:"+ '\x1b[0m' + sql + " [" + params + "] ")
        //预处理
		sql = prepareSql(sql, params);
		console.log("替换后的sql语句:" + sql)
		plus.sqlite.selectSql({
            name: database_name,
            sql: sql,
            success(e){
                reslove(convertDbObjBiz(e))
				console.log("成功",e)
            },
            fail(e){
                console.error("失败", e)
                _reject(e)
            }
        })
    })
}
//创建表
function createTable(){
	console.log("开始初始化数据库表...")
	return new Promise( async (resolve, reject) => {
		for(const item of add_table){
			await executeSQL(item)
		}
		for(const item of add_index){
			await executeSQL(item)
		}
		for(const item of alter_tables){
			const fieldList = await queryAll("PRAGMA table_info(" + item.tableName + ");", [])
			const field = fieldList.some((row) => row.name === item.field)
			if(!field){
				await executeSQL(item.sql)

			}
		}
		console.log("初始化数据表完成...")
		resolve();
	})
}
const convertDbObjBiz = (data) => {
    if(!data){
        return null;
    }
    const bizData = {};
    for(let item in data){
        bizData[toCameCase(item)] = data[item]
    }
    return bizData;
}
const toCameCase = (str) => {
    return str.replace(/_([a-z])/g, function(_match, p){
        return String.fromCharCode(p.charCodeAt(0) - 32)
    })
}
//查询所有表，并更改字段为驼峰命名法
const initTableColumnsMap = async () => {
    let sql = `select name from sqlite_master where type = 'table' and name != 'sqlite_sequence'`;
    let tables = await queryAll(sql, []);
    for(let i = 0; i < tables.length; i++){
        sql = `PRAGMA table_info(${tables[i].name})`;
        let columns = await queryAll(sql, [])
        const columnMapItem = {};
        for(let j = 0; j< columns.length; j++){
            columnMapItem[toCameCase(columns[j].name)] = columns[j].name
        }
        globalColumnsMap[tables[i].name] = columnMapItem;
    }
}
//初始化本地数据库
const initDb = async () => {
	//判断数据库是否打开
	if(isOpen(database_name, '_doc/mydata.db')){
		console.log("数据库已打开...")
	}else{
		//打开数据库
		await openSqlite();
	}
    await createTable();
    await initTableColumnsMap();
}
initDb()

//对sql语句进行预处理，将?替换为参数
function prepareSql(sql, params){
	if(params){
		sql = sql.replace(/\?/g, function() {
			const paramValue = params.shift();
			// 转义参数中的双引号
			const escapedValue = typeof paramValue === 'string' ? paramValue.replace(/"/g, "'") : paramValue;
			return '"' + escapedValue + '"';
		});
	}
	return sql;
}
//把这些方法导出去
export{
	openSqlite,
	isOpen,
	closeSQL,
	executeSQL,
	createTable,
	queryOne,
	update,
	insetOrIgnore,
	selectSQL,
	selectSQLParam,
	insertOrReplace,
	executeSQLParam,
	queryAll,
	queryCount
}

